<?php
namespace Home\Controller;

use Think\Controller;

class IndexController extends Controller
{

    /**
     * 后台管理员登陆
     */
    public function login()
    {
        if (IS_POST) {
            $username = I('post.username');
            $pwd = I('post.password');
            $time = NOW_TIME;

            if (md5($username . $pwd . $time) != md5('hdqb_admin' . '456123' . $time)) {
                $this->error('用户名或者密码错误', U('Index/login'), 2);
            }

            session('USER_INFO', md5($username . $pwd . 'hdqb'));
            $this->success('登陆成功', U('Index/index'));
        } else {
            $this->display();
        }
    }

    /**
     * 管理员退出
     */
    public function logout()
    {
        session('USER_INFO', null);
        redirect(U('Index/login'));
    }

    /**
     * 客户回头率
     */
    public function index()
    {
        $sql = 'select  distinct  tt3.merno,tt3.eczs/tt2.zs as ecbf,qb_merlist.meraccount,mername from tt2,tt3,qb_merlist where tt2.merno=tt3.merno  and qb_merlist.merno=tt3.merno having ecbf between 0.2 and 0.99 order by ecbf desc';
        $rows = M()->query($sql);
        $pageTotal = C('PAGE_TOTAL');
        
        if(!$count=count($rows)){
            $count = 0;
        }
        
        $page_model = new \Think\Page($count, $pageTotal);
        $page = $page_model->show(); 
        $limit = $this->pages($count, $pageTotal);      
        $rows = M()->query($sql.$limit);
        $data = array();

        foreach ($rows as $v) {
            $tmp = explode('.', $v['ecbf']);
            $num = substr($tmp[1], 0, 4);
            $v['ecbf'] = number_format($num / 100,2);
            $data[] = $v;
        }
        $this->assign('page', $page);
        $this->assign('rows', $data);
        $this->assign('count', $count);
        $this->display();
    }

    /**
     * 交易百分比
     */
    public function tradePercent()
    {

        $sql = 'select ct2.merno,ct2.smzs/ct3.zs as smbfb ,meraccount,mername from ct2,ct3 ,qb_merlist where ct2.merno=ct3.merno and qb_merlist.merno=ct2.merno having smbfb between 0.1 and 0.99 order by smbfb desc';
        $rows = M()->query($sql);
        $pageTotal = C('PAGE_TOTAL');
        
        if(!$count=count($rows)){
            $count = 0;
        }
        
        $limit = $this->pages($count, $pageTotal);  
        $rows = M()->query($sql.$limit);
        $data = array();

        foreach ($rows as $v) {
            $tmp = explode('.', $v['smbfb']);
            $num = substr($tmp[1], 0, 4);
            $v['smbfb'] = number_format($num / 100,2);
            $data[] = $v;
        }
        
        $page_model = new \Think\Page($count, $pageTotal);
        $page = $page_model->show();
        $this->assign('page', $page);
        $this->assign('rows', $data);
        $this->assign('count', $count);
        $this->display();
    }

    /**
     * 3秒内的最新百分比
     */
    public function lastedPercent()
    {

        $sql = 'select bt4.merno,bt4.gbbzs/bt3.zs as gbbbfb ,meraccount,mername from bt3,bt4 ,qb_merlist where bt3.merno=bt4.merno and bt4.merno=qb_merlist.merno
 order by gbbbfb ';
        $rows = M()->query($sql);
        
        if(!$count=count($rows)){
            $count = 0;
        }
        
        $pageTotal = C('PAGE_TOTAL');
        $limit = $this->pages($count, $pageTotal);
        $rows = M()->query($sql.$limit);
        $data = array();

        foreach ($rows as $v) {
            $tmp = explode('.', $v['gbbbfb']);
            $num = substr($tmp[1], 0, 4);
            $v['gbbbfb'] = number_format($num / 100,2);
            $data[] = $v;
        }

        $page_model = new \Think\Page($count, $pageTotal);
        $page = $page_model->show();
        $this->assign('page', $page);
        $this->assign('rows', $data);
        $this->assign('count', $count);
        $this->display();
    }

    /**
     *时间段百分比
     */
    public function timePercent()
    {
        $sql = 'select sjd3.merno,sjd3.sxzs2/sjd1.jyzs as jybfb,meraccount,mername from sjd1,sjd3,qb_merlist where sjd1.merno=sjd3.merno
 and sjd3.merno=qb_merlist.merno  having jybfb <=0.5 order by jybfb desc';
        $rows = M()->query($sql);
        
        if(!$count=count($rows)){
            $count = 0;
        }
        $pageTotal = C('PAGE_TOTAL');
        $limit = $this->pages($count, $pageTotal);
        $rows = M()->query($sql.$limit);
        $data = array();

        foreach ($rows as $v) {
            $tmp = explode('.', $v['jybfb']);
            $num = substr($tmp[1], 0, 4);
            
            $v['jybfb'] = number_format($num / 100,2);
            $data[] = $v;
        }

        $page_model = new \Think\Page($count, $pageTotal);
        $page = $page_model->show();
        $this->assign('page', $page);
        $this->assign('rows', $data);
        $this->assign('count', $count);
        $this->display();
    }

    /**
     * 搜索
     * @param  string $keyword
     */
    public function search($keyword)
    {
        //TODO::search
    }

    /**
     * 交易排行榜
     */
    public function tradeList()
    {
        $sql = 'select qb_orders.merno,qb_merlist.meraccount,count(*) as jyzs ,qb_merlist.mername,sum(qb_orders.all_price) as jyje from qb_orders,qb_merlist
 where left(qb_orders.up_time,10) = left(now(),10)  and qb_orders.status<>\'待支付\' and qb_merlist.merno=qb_orders.merno group by qb_orders.merno order by
 jyzs desc';
        $rows = M()->query($sql);
        
        if(!$count=count($rows)){
            $count = 0;
        }
        
        $pageTotal = C('PAGE_TOTAL');
        $limit = $this->pages($count, $pageTotal);
        $rows = M()->query($sql.$limit);
        $page_model = new \Think\Page($count, $pageTotal);
        $page = $page_model->show();
        $this->assign('page', $page);
        $this->assign('rows', $rows);
        $this->assign('count', $count);
        $this->display();
    }

    /**
     * 获取用户信息
     * @param  int $id
     */
    public function getUserInfo($id)
    {
        $user_sql = 'select merno,userid,usermobile from qb_user where merno='.$id;
        $user_info = M()->query($user_sql);
        if(!$count=count($user_info)){
            $count = 0;
        }
        
        $pageTotal = C('PAGE_TOTAL');
        $limit = $this->pages($count, $pageTotal);
        $page_model = new \Think\Page($count, $pageTotal);
        $page = $page_model->show();
        $rows = M()->query($user_sql.$limit);
        $this->assign('page', $page);
        $this->assign('count', $count);
        $this->assign('rows', $rows);
        $this->display();
    }
    
    /**
     * 交易数量统计
     */
    public function tradeAmount()
    {
        // 如果数据不存在查询数据库缓存30分钟
        $data = S('JYTJ');
        if(!$data){
            // 交易笔数
            $total_trade_sql = 'select count(*) as trade_sum from qb_orders where status<>\'待支付\'';
            $trade_sum = M()->query($total_trade_sql);

            // 总交易用户数
            $total_trade_users_sql = 'select count(distinct userid) as total_users  from qb_orders where status<> \'待支付\'';
            $total_users = M()->query($total_trade_users_sql);
            //var_dump($total_users);exit;
            // 消费2次的用户
            $two_trade_sql = 'select count(*) as two_times from (select merno ,userid,count(*) as zs from qb_orders where status <>\'待支付\' group by userid having zs=2) a';
            $two_times = M()->query($two_trade_sql);

            // 消费3次的用户
            $three_trade_sql = 'select count(*) as three_times from  (select merno ,userid,count(*) as zs from qb_orders where status <>\'待支付\' group by userid having zs=3) b';
            $three_times = M()->query($three_trade_sql);

            // 消费4次的用户
            $four_trade_sql = 'select count(*) as four_times from (select merno ,userid,count(*) as zs from qb_orders where status <>\'待支付\' group by userid having zs=4) c';
            $four_times = M()->query($four_trade_sql);

            // 消费5次的用户
            $five_trade_sql = 'select count(*) as five_times from (select merno ,userid,count(*) as zs from qb_orders where status <>\'待支付\' group by userid having zs=5) d';
            $five_times = M()->query($five_trade_sql);

            // 消费6次的用户
            $six_trade_sql = 'select count(*) as six_times from (select merno ,userid,count(*) as zs from qb_orders where status <>\'待支付\' group by userid having zs=6) e';
            $six_times = M()->query($six_trade_sql);

            // 消费7次的用户
            $seven_trade_sql = 'select count(*) as seven_times from (select merno ,userid,count(*) as zs from qb_orders where status <>\'待支付\' group by userid having zs=7) f';
            $seven_times = M()->query($seven_trade_sql);
            
            $data = array(
                'trade_sum' => $trade_sum[0]['trade_sum'],
                'total_users' => $total_users[0]['total_users'],
                'two_times' => $two_times[0]['two_times'],
                'three_times' => $three_times[0]['three_times'],
                'four_times' => $four_times[0]['four_times'],
                'five_times' => $five_times[0]['five_times'],
                'six_times' => $six_times[0]['six_times'],
                'seven_times' => $seven_times[0]['seven_times'],
            );
            S('JYTJ', $data, 60*30);
        }
        
        $this->assign($data);
        $this->display();
    }
    
    /**
     *  分页
     *  @param int $count 分页总数
     *  @param int $pageTotal 每页显示的条数
     *  return string  limit语句
     */
    private function pages($count, $pageTotal)
    {
        
        $p = isset($_GET['p']) ? $_GET['p'] : 1;
        $total_pages = ceil($count/$pageTotal);
        
        if($p<0){
            $p = 1;
        }elseif($p>$total_pages){
            $p = $total_pages;
        }
        
        $start = ($p-1)*$pageTotal;
        $limit = ' limit ' . $start . ',' . $pageTotal;
        return $limit;
    }

    /**
     *  商户资金和本金信息
     */
    public function merchantFund()
    {
        $count = M()->table('qb_mer_cash')->count('mer_no');
        $pageTotal = C('PAGE_TOTAL');  
        $Page       = new \Think\Page($count,$pageTotal);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show       = $Page->show();// 分页显示输出
        $list = M()->table('qb_mer_cash')->limit($Page->firstRow.','.$Page->listRows)->select();
        $this->assign('page', $show);
        $this->assign('count', $count);
        $this->assign('rows', $list);
        $this->display();
    }

    /**
     * 导出excel
     */
    public function excelOutput()
    {

        $xlsName  = "商户本金和补贴信息";
        $list = M()->table('qb_mer_cash')->select();
        $xlsCell  = array(
            array('mer_no','会员编号'),
            array('mername','商户名'),
            array('meraccount','商户账号'),
            array('city','所在区域'),
            array('bj','本金'),
            array('qzbt','七折补贴'),
            array('bzbt','八折补贴'),
            array('jzbt','九折补贴'),
            array('bt','免费补贴'),
            array('tx','提现'),
            array('addtime','入驻时间'),
            
        );

        if($this->exportExcel($xlsName,$xlsCell,$list)){
            $this->success('导出成功', U('Index/merchantFund'));
            exit();
        }

    }
    
    
    /*public function output_db(){

        //exit();
        $xlsName  = "商户本金和补贴信息";
        $where['mer_type']  =   array('in','1,5');
        
        $start  =   S('limit') ? S('limit') : 0;
        
        
        $list = M()->table('qb_merlist')->field('MerNo,City,MerName,AddTime,MerAccount')->limit($start,10)->where($where)->select();
        S('limit',$start + 10);
        //var_dump($list);exit();

        
        foreach ($list as $key => $value) {
            
            //获取补贴
            $whereBill['mer_no']=   $value['merno'];

            $whereBill['tags']  =   array('neq','用户在线消费获得-商户获得-用户支付');
            $butie    =   M()->table('qb_billback')->cache(true,60 *60)->where($whereBill)->sum('money');

            $list[$key]['bt'] = $butie ? $butie/100 : '0';


            //支付
            

            $wherePay['mer_no'] = $value['merno'];
            $benjin    =   M()->table('qb_coupon_pay')->cache(true,60*60)->where($wherePay)->sum('pay_money');

            //echo M()->getLastSql();exit();

            $list[$key]['bj'] = $benjin ? $benjin : '0';
            

            //提现金额

            $whereTx['MerId']  = $value['merno'];
            $whereTx['status'] = '完结';
            $tx    =   M()->table('qb_mer_drawals')->cache(true,60*60)->where($whereTx)->sum('Amount');
            $list[$key]['tx'] = $tx ? $tx : '0';

        }

        

    }*/
    

    /**
     +----------------------------------------------------------
     * Export Excel | 2013.08.23
     * Author:HongPing <hongping626@qq.com>
     +----------------------------------------------------------
     * @param $expTitle     string File name
     +----------------------------------------------------------
     * @param $expCellName  array  Column name
     +----------------------------------------------------------
     * @param $expTableData array  Table data
     +----------------------------------------------------------
     */
    public function exportExcel($expTitle,$expCellName,$expTableData,$fileName){
        
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        
        //$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]); 
        } 
          // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
          for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
          }             
        }  
        
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
        $objWriter->save('php://output'); 
        return true;   
    }

    /**
     *   商户统计
     */
    public function exp_mer()
    {
        
        $where['AccType']   =   '3';
        
        $list = M()->table('qb_merlist')->where($where)->field('merno,mername')->select();
        
        foreach ($list as $key => $value) {
            
            $whereAid['AID']        =   $value['merno'];
            $whereAid['AddTime']    =   array('like','2016-07%');
            $whereAid['mer_type']   =   '5';
            
            $list[$key]['num']      =   M()->table('qb_merlist')->where($whereAid)->count('id');
            
        }
        
        $xlsName  = "商户统计";
        $xlsCell  = array(
            array('merno','商户编号'),
            array('mername','商户名'),
            array('num','七月新增量'),
        
            
        );

        if($this->exportExcel($xlsName,$xlsCell,$list)){
            $this->success('导出成功', U('Index/index'));
            exit();
        }
        
    }

    /**
     *   根据商户账户查询
     */
    public function searchByAccount($account)
    {
        $where = array(
                'meraccount' => $account,
            );
        $count = M()->table('qb_mer_cash')->where($where)->count('mer_no');
        $pageTotal = C('PAGE_TOTAL');  
        $Page       = new \Think\Page($count,$pageTotal);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show       = $Page->show();// 分页显示输出
        $list = M()->table('qb_mer_cash')->limit($Page->firstRow.','.$Page->listRows)->where($where)->select();
        $this->assign('page', $show);
        $this->assign('count', $count);
        $this->assign('rows', $list);
        $this->display('merchantFund');
    }
}